# GF_PY3_CLASS_Finance_Indicator_KDJ_s_SQLITE3.py
# Create By GF 2024-01-22 18:16

import sqlite3  # Import Validation: SQLite3 2.6.0 from Python 3.12.0 / Requires SQLite 3.7.15 or Newer
# ..................................................
import GF_PY3_CLASS_Finance_Indicator_KDJ

# ##################################################

class Finance_Indicator_KDJ_s_SQLITE3(GF_PY3_CLASS_Finance_Indicator_KDJ.Finance_Indicator_KDJ):

    def __init__(self, SQLITE3_CONNECT, DB_TABLE:str):

        # 注意:
        # 需要在函数外创建 SQLite 3 数据库连接 (以便于复用及提高代码效率)
        # 示例:
        # >>> SQLITE3_CONNECT = sqlite3.connect("./EXAMPLE.db")  # 连接到 SQLite 3 数据库 (如果文件不存在, 会自动创建)
        # >>> SQLITE3_CONNECT.commit()                           # SQLIT3 提交事务 (查询无需 Commit, 删改需要 Commit 使事务生效)
        # >>> SQLITE3_CONNECT.close()                            # SQLIT3 关闭 Connection

        self.SQLITE3_CONNECT = SQLITE3_CONNECT
        self.DB_TABLE:str = DB_TABLE
        # ..........................................
        self.JSON_Queue_of_High:list = []   # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_Low:list = []    # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_Close:list = []  # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_KDJ_K:list = []  # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。
        self.JSON_Queue_of_KDJ_D:list = []  # 子类 (派生类) 会覆盖父类 (基类) 的 __init__ 方法。

    def UPDATE_OR_INSERT_KDJ_K(self, ID:int, ROW_NUM:int, RSV_Prd:int, K_Prd:int, High:float, Low:float, Close:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()  # 创建一个 Cursor 对象并调用其 execute() 方法执行 SQL 命令
        # ..........................................
        KDJ_K_Value = self.KDJ_K(ROW_NUM = ROW_NUM, RSV_Prd = RSV_Prd, K_Prd = K_Prd, High = High, Low = Low, Close = Close)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        KDJ_K_Value = "NULL" if (KDJ_K_Value == None) else round(KDJ_K_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()  # 获取查询到的第 1 个结果
        ID_COUNT = Queried[0]        # 获取查询结果第 1 个值
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, kdj_k) VALUES ({ID}, {KDJ_K_Value});")
            Affected_Numbers = Cursor.rowcount  # 获取受影响的行数
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET kdj_k = {KDJ_K_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount  # 获取受影响的行数
        # ..........................................
        Cursor.close()  # SQLIT3 关闭 Cursor
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": KDJ_K_Value}

    def UPDATE_OR_INSERT_KDJ_D(self, ID:int, ROW_NUM:int, RSV_Prd:int, D_Prd:int, K_Val:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        KDJ_D_Value = self.KDJ_D(ROW_NUM = ROW_NUM, RSV_Prd = RSV_Prd, D_Prd = D_Prd, K_Val = K_Val)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        KDJ_D_Value = "NULL" if (KDJ_D_Value == None) else round(KDJ_D_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, kdj_d) VALUES ({ID}, {KDJ_D_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET kdj_d = {KDJ_D_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": KDJ_D_Value}

    def UPDATE_OR_INSERT_KDJ_J(self, ID:int, K_Val:float, D_Val:float) -> dict:

        Affected_Numbers = 0
        # ..........................................
        Cursor = self.SQLITE3_CONNECT.cursor()
        # ..........................................
        KDJ_J_Value = self.KDJ_J(K_Val = K_Val, D_Val = D_Val)
        # ..........................................
        # Possible Errors: sqlite3.OperationalError: no such column: None
        # SQLite 3 does not Support None, NULL should be Used Instead.
        KDJ_J_Value = "NULL" if (KDJ_J_Value == None) else round(KDJ_J_Value, 4)
        # ..........................................
        Cursor.execute(f"SELECT COUNT(*) AS ID_COUNT FROM {self.DB_TABLE} WHERE id = {ID}")
        Queried = Cursor.fetchone()
        ID_COUNT = Queried[0]
        # ..........................................
        if (ID_COUNT == 0):
            Cursor.execute(f"INSERT INTO {self.DB_TABLE} (id, kdj_j) VALUES ({ID}, {KDJ_J_Value});")
            Affected_Numbers = Cursor.rowcount
        if (ID_COUNT >= 1):
            Cursor.execute(f"UPDATE {self.DB_TABLE} SET kdj_j = {KDJ_J_Value} WHERE id = {ID};")
            Affected_Numbers = Cursor.rowcount
        # ..........................................
        Cursor.close()
        # ..........................................
        return {"affected_numbers": Affected_Numbers, "value": KDJ_J_Value}

# EOF Signed by GF.
